create or replace function update_human(
        p_id integer,
        p_name varchar(128),
        p_isStudent boolean,
        p_twitterId varchar(128),
        p_linkedinId varchar(128),
        p_facebookId varchar(128),
        p_latitude latitude_t,
        p_longitude longitude_t,
        p_x integer,
        p_y integer,
        p_subject varchar(128),
        p_userId integer,
        p_classId integer)
    returns void as
$$
begin
    update humans set
        userId =        case when p_userId=0
                            then userId
                            else p_userId
                        end,
        name =          case when p_name=''
                            then name
                            else p_name
                        end,
        isStudent =     p_isStudent,
        twitterId =     case when p_twitterId=''
                            then twitterId
                            else p_twitterId
                        end,
        linkedinId =    case when p_linkedinId=''
                            then linkedinId
                            else p_linkedinId
                        end,
        facebookId =    case when p_facebookId=''
                            then facebookId
                            else p_facebookId
                        end,
        geoCoord =      case when (p_latitude::latitude_t, p_longitude::longitude_t)::geoCoord_t=(0.0::latitude_t,0.0::longitude_t)
                            then geoCoord
                            else (p_latitude::latitude_t, p_longitude::longitude_t)::geoCoord_t
                        end,
        photoCoord =    case when (p_x,p_y)::photoCoord_t=(0,0)::photoCoord_t
                            then photoCoord
                            else (p_x,p_y)::photoCoord_t
                        end,
        subject =       case when p_subject=''
                            then subject
                            else p_subject
                        end,
        classId =       case when p_classId=0
                            then classId
                            else p_classId
                        end
    where id=p_id;
end;
$$
language plpgsql;

create or replace function set_human_user_id(p_id integer, p_userId integer)
    returns void as
$$
begin
    update humans set
        userId = p_userId
    where id = p_id;
end;
$$
language plpgsql;

create or replace function delete_human(p_id integer)
    returns void as
$$
begin
    delete from humans where id = p_id;
end;
$$
language plpgsql;

create or replace function get_human_by_user_id(p_userId integer)
    returns text as
$$
begin
    return row_to_json(humans) from humans where userId=p_userId;
end;
$$
language plpgsql;

create or replace function get_all_humans()
    returns text as
$$
begin
    return array_to_json(array(select row_to_json(humans) from humans));
end;
$$
language plpgsql;

create or replace function get_humans_to_blur()
    returns text as
$$
begin
    return array_to_json(array(
        select row_to_json(humans.*)
            from humans
                left outer join users
              on humans.userId=users.id
        where (humans.isStudent=true)
          and (users.isActive is not true)));
end;
$$
language plpgsql;

create or replace function get_unblurred_humans()
    returns text as
$$
begin
    return array_to_json(array(
        select row_to_json(humans.*)
            from humans
              left outer join users
            on humans.userID=users.Id
        where (humans.isStudent=false)
           or (users.isActive=true)));
end;
$$
language plpgsql;

create or replace function create_human(
        p_name varchar(128),
        p_isStudent boolean,
        p_twitterId varchar(128),
        p_linkedinId varchar(128),
        p_facebookId varchar(128),
        p_latitude latitude_t,
        p_longitude longitude_t,
        p_x integer,
        p_y integer,
        p_subject varchar(128),
        p_classId integer)
    returns text as
$$
begin
    insert into humans(
            name,
            isStudent,
            twitterId,
            linkedinId,
            facebookId,
            geoCoord,
            photoCoord,
            subject,
            classId)
        values (
            p_name,
            p_isStudent,
            case when p_twitterId=''
                    then null
                    else p_twitterId
            end,
            case when p_linkedinId=''
                    then null
                    else p_linkedinId
            end,
            case when p_facebookId=''
                    then null
                    else p_facebookId
            end,
            case when (p_latitude::latitude_t, p_longitude::longitude_t)::geoCoord_t=(0.0::latitude_t,0.0::longitude_t)
                    then null
                    else (p_latitude::latitude_t, p_longitude::longitude_t)::geoCoord_t
            end,
            case when (p_x,p_y)::photoCoord_t=(0,0)::photoCoord_t
                    then null
                    else (p_x,p_y)::photoCoord_t
            end,
            case when p_subject=''
                    then null
                    else p_subject
            end,
            case when p_classId=0
                    then null
                    else p_classId
            end);
    return cast(ret as text) from currval(pg_get_serial_sequence('humans', 'id')) as ret;
end;
$$
language plpgsql;
